﻿Imports System.Data.SqlClient

Public Class frmSlipMAC
    Public EditMode As Boolean
    Public EditSNo As Integer
    Public EditSlipNo As String


    Private Sub frmSlipMAC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillInsurCompanies()

        'Check if screen is opened in Edit Mode
        If EditMode = True Then
            Me.btnSave.Text = "Update"

            FillSlip()
        End If


    End Sub



    Sub FillSlip()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Slips Where SNo=" & EditSNo, cnn)
            Dim Reader As SqlDataReader

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.txtC1.Text = Reader.Item("C1")
                Me.txtC2.Text = Reader.Item("C2")
                Me.txtC3.Text = Reader.Item("C3")
                Me.txtC4.Text = Reader.Item("C4")
                Me.txtC5.Text = Reader.Item("C5")
                Me.txtC6.Text = Reader.Item("C6")
                Me.txtC7.Text = Reader.Item("C7")
                Me.txtC8.Text = Reader.Item("C8")
                Me.txtC9.Text = Reader.Item("C9")
                Me.txtC10.Text = Reader.Item("C10")
                Me.txtC11.Text = Reader.Item("C11")
                Me.txtC12.Text = Reader.Item("C12")
                Me.txtC13.Text = Reader.Item("C13")
                Me.txtC14.Text = Reader.Item("C14")
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub


    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.GridInsuranceCompanies.Rows.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.GridInsuranceCompanies.Rows.Add({False, InsurComp})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub


    Public ClassOfBuiness, LineOfBuiness, Abbreviation As String

    Sub Clear()
        Me.txtC1.Clear()

        Me.txtC2.Clear()
        Me.txtC3.Clear()
        Me.txtC4.Clear()
        Me.txtC5.Clear()
        Me.txtC6.Clear()
        Me.txtC7.Clear()
        Me.txtC8.Clear()
        Me.txtC9.Clear()
        Me.txtC10.Clear()
        Me.txtC11.Clear()
        Me.txtC12.Clear()
        Me.txtC13.Clear()
        Me.txtC14.Clear()
        'Me.txtC16.Clear()
        'Me.txtC17.Clear()
        'Me.txtC18.Clear()
        'Me.txtC19.Clear()
        'Me.txtC20.Clear()
        'Me.txtC21.Clear()
        For Each row As DataGridViewRow In Me.GridInsuranceCompanies.Rows
            row.Cells(Me.DGColSelect.Index).Value = False
        Next


        Me.txtC1.Focus()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If Me.txtC1.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC1, "Please fill in all details")
            Me.txtC1.Focus()

        ElseIf Me.txtC2.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC2, "Please fill in all details")
            Me.txtC2.Focus()

        ElseIf txtC3.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC3, "Please fill in all details")
            Me.txtC3.Focus()

        ElseIf txtC4.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC4, "Please fill in all details")
            Me.txtC4.Focus()

        ElseIf Me.txtC5.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC5, "Please fill in all details")
            Me.txtC5.Focus()

        ElseIf Me.txtC6.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC6, "Please fill in all details")
            Me.txtC6.Focus()

        ElseIf Me.txtC7.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC7, "Please fill in all details")
            Me.txtC7.Focus()

        ElseIf Me.txtC8.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC8, "Please fill in all details")
            Me.txtC8.Focus()

        ElseIf Me.txtC9.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC9, "Please fill in all details")
            Me.txtC9.Focus()

        ElseIf Me.txtC10.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC10, "Please fill in all details")
            Me.txtC10.Focus()

        ElseIf Me.txtC11.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC11, "Please fill in all details")
            Me.txtC11.Focus()


        ElseIf Me.txtC12.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC12, "Please fill in all details")
            Me.txtC12.Focus()

        ElseIf Me.txtC13.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC13, "Please fill in all details")
            Me.txtC13.Focus()

        ElseIf Me.txtC14.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC14, "Please fill in all details")
            Me.txtC14.Focus()

        Else

            If EditMode = False Then
                SaveNewSlip()
            Else
                UpdateSlip()
            End If

        End If

    End Sub
    Sub SaveNewSlip()
        Try
            Me.Cursor = Cursors.WaitCursor

            'Check if one or more insurance companies are selected
            Dim InsurCompanies As ArrayList = GetSelInsurCompanies()

            If InsurCompanies.Count = 0 Then
                MsgBox("Please select the insurance company(ies) the slip is issued for")
                Me.Cursor = Cursors.Default
                Exit Sub
            End If

            Dim DocSNo, Year As Integer
            Dim SlipNo As String
            Dim cmd As New SqlCommand
            Dim Trans As SqlTransaction
            Dim X As Integer = 1

            cnn.Open()
            Trans = cnn.BeginTransaction
            cmd.Transaction = Trans
            cmd.Connection = cnn

            'Get Year
            cmd.CommandText = "Select Year(GetDate())"
            Year = CInt(cmd.ExecuteScalar)

            'Get Doc SNo
            cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Slips Where Branch=@Branch and Year(TransDate)=Year(GetDate()) and Abbreviation=N'" & Abbreviation & "'"
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@Branch", Branch)
            DocSNo = CInt(cmd.ExecuteScalar) + 1

            For Each InsurComp As String In InsurCompanies
                'Create SlipNo
                SlipNo = "Slip-" & BranchCode & "-" & Abbreviation & "-" & Year & "-" & DocSNo & "-D" & X

                'Save slip
                cmd.CommandText = "Insert Into Slips " & _
                                  "(Branch,SlipNo,LineOfBuiness,Abbreviation,Type,InsuranceCompany,Status,DocSNo,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,UserName) " & _
                                  "Values " & _
                                  "(@Branch,@SlipNo,@LineOfBuiness,@Abbreviation,@Type,@InsuranceCompany,@Status,@DocSNo,@C1,@C2,@C3,@C4,@C5,@C6,@C7,@C8,@C9,@C10,@C11,@C12,@C13,@C14,@UserName)"

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@Branch", Branch)
                cmd.Parameters.AddWithValue("@SlipNo", SlipNo)
                cmd.Parameters.AddWithValue("@LineOfBuiness", LineOfBuiness)
                cmd.Parameters.AddWithValue("@Abbreviation", Abbreviation)
                cmd.Parameters.AddWithValue("@Type", "Insurance")
                cmd.Parameters.AddWithValue("@InsuranceCompany", InsurComp)
                cmd.Parameters.AddWithValue("@Status", "Pending")
                cmd.Parameters.AddWithValue("@DocSNo", DocSNo)
                cmd.Parameters.AddWithValue("@C1", Me.txtC1.Text)
                cmd.Parameters.AddWithValue("@C2", Me.txtC2.Text.Trim)
                cmd.Parameters.AddWithValue("@C3", Me.txtC3.Text)
                cmd.Parameters.AddWithValue("@C4", Me.txtC4.Text.Trim)
                cmd.Parameters.AddWithValue("@C5", Me.txtC5.Text)
                cmd.Parameters.AddWithValue("@C6", Me.txtC6.Text.Trim)
                cmd.Parameters.AddWithValue("@C7", Me.txtC7.Text.Trim)
                cmd.Parameters.AddWithValue("@C8", Me.txtC8.Text.Trim)
                cmd.Parameters.AddWithValue("@C9", Me.txtC9.Text.Trim)
                cmd.Parameters.AddWithValue("@C10", Me.txtC10.Text.Trim)
                cmd.Parameters.AddWithValue("@C11", Me.txtC11.Text.Trim)
                cmd.Parameters.AddWithValue("@C12", Me.txtC12.Text.Trim)
                cmd.Parameters.AddWithValue("@C13", Me.txtC13.Text.Trim)
                cmd.Parameters.AddWithValue("@C14", Me.txtC14.Text.Trim)
                cmd.Parameters.AddWithValue("@UserName", CurrentUser)
                cmd.ExecuteNonQuery()

                X += 1
            Next

            Trans.Commit()
            cnn.Close()

            MsgBox("Saved Successfully!")

            Dim ChosenFolderPath, SlipSNo As String

            'Print
            If Me.FolderBrowserDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                ChosenFolderPath = Me.FolderBrowserDialog1.SelectedPath
            Else
                Me.Cursor = Cursors.Default
                Exit Sub
            End If

            X = 1
            For Each InsurComp As String In InsurCompanies
                SlipSNo = "Slip-" & BranchCode & "-" & Abbreviation & "-" & Year & "-" & DocSNo & "-D" & X

                'PrintSlip(SlipSNo, Abbreviation)

                Dim dap As New SqlDataAdapter("Select * From Slips Where SlipNo=@SlipNo", cnn)
                Dim das As New DSSlips

                dap.SelectCommand.Parameters.AddWithValue("@SlipNo", SlipSNo)
                dap.Fill(das, "Slips")

                Dim rpt As New SlipMAC
                rpt.SetDataSource(das)
                rpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, _
                                 ChosenFolderPath & "\" & InsurComp & " - " & SlipSNo & ".pdf")
                rpt.Dispose()
                X += 1
            Next

            System.Diagnostics.Process.Start(ChosenFolderPath)
            Clear()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub
    Sub UpdateSlip()
        Try
            Me.Cursor = Cursors.WaitCursor

            'Check if one or more insurance companies are selected
            Dim InsurCompanies As ArrayList = GetSelInsurCompanies()

            If InsurCompanies.Count = 0 Then
                MsgBox("Please select the insurance company(ies) the slip is issued for")
                Me.Cursor = Cursors.Default
                Exit Sub
            End If

            Dim cmd As New SqlCommand
            Dim Trans As SqlTransaction
            Dim X As Integer = 1

            cnn.Open()
            Trans = cnn.BeginTransaction
            cmd.Transaction = Trans
            cmd.Connection = cnn


            'Save slip
            cmd.CommandText = "Update Slips Set " & _
                              "C1=@C1,C2=@C2,C3=@C3,C4=@C4,C5=@C5,C6=@C6,C7=@C7,C8=@C8,C9=@C9,C10=@C10,C11=@C11,C12=@C12,C13=@C13,C14=@C14,UpdatedBy=@UpdatedBy,UpdatedOn=GetDate() " & _
                              "Where SNo=" & EditSNo

            cmd.Parameters.AddWithValue("@C1", Me.txtC1.Text)
            cmd.Parameters.AddWithValue("@C2", Me.txtC2.Text.Trim)
            cmd.Parameters.AddWithValue("@C3", Me.txtC3.Text)
            cmd.Parameters.AddWithValue("@C4", Me.txtC4.Text.Trim)
            cmd.Parameters.AddWithValue("@C5", Me.txtC5.Text)
            cmd.Parameters.AddWithValue("@C6", Me.txtC6.Text.Trim)
            cmd.Parameters.AddWithValue("@C7", Me.txtC7.Text.Trim)
            cmd.Parameters.AddWithValue("@C8", Me.txtC8.Text.Trim)
            cmd.Parameters.AddWithValue("@C9", Me.txtC9.Text.Trim)
            cmd.Parameters.AddWithValue("@C10", Me.txtC10.Text.Trim)
            cmd.Parameters.AddWithValue("@C11", Me.txtC11.Text.Trim)
            cmd.Parameters.AddWithValue("@C12", Me.txtC12.Text.Trim)
            cmd.Parameters.AddWithValue("@C13", Me.txtC13.Text.Trim)
            cmd.Parameters.AddWithValue("@C14", Me.txtC14.Text.Trim)
            cmd.Parameters.AddWithValue("@UpdatedBy", CurrentUser)
            cmd.ExecuteNonQuery()

            Trans.Commit()
            cnn.Close()

            MsgBox("Saved Successfully!")

            PrintSlip(EditSlipNo, Abbreviation)
            Clear()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub


    Function GetSelInsurCompanies() As ArrayList
        Try
            Dim InsurCompanies As New ArrayList

            For Each InsurComp As DataGridViewRow In Me.GridInsuranceCompanies.Rows
                If CBool(InsurComp.Cells(Me.DGColSelect.Index).Value) = True Then
                    InsurCompanies.Add(InsurComp.Cells(Me.DGColInsuranceCompany.Index).Value)
                End If
            Next

            Return InsurCompanies
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Function


    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Clear()
    End Sub


    Private Sub btnPreview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPreview.Click
        Me.ErrProv.Clear()
        If Me.txtC1.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC1, "Please fill in all details")
            Me.txtC1.Focus()

        ElseIf Me.txtC2.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC2, "Please fill in all details")
            Me.txtC2.Focus()

        ElseIf txtC3.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC3, "Please fill in all details")
            Me.txtC3.Focus()

        ElseIf Me.txtC4.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC4, "Please fill in all details")
            Me.txtC4.Focus()

        ElseIf Me.txtC5.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC5, "Please fill in all details")
            Me.txtC5.Focus()

        ElseIf Me.txtC6.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC6, "Please fill in all details")
            Me.txtC6.Focus()

        ElseIf Me.txtC7.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC7, "Please fill in all details")
            Me.txtC7.Focus()

        ElseIf Me.txtC8.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC8, "Please fill in all details")
            Me.txtC8.Focus()

        ElseIf Me.txtC9.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC9, "Please fill in all details")
            Me.txtC9.Focus()

        ElseIf Me.txtC10.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC10, "Please fill in all details")
            Me.txtC10.Focus()

        ElseIf Me.txtC11.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC11, "Please fill in all details")
            Me.txtC11.Focus()

        ElseIf Me.txtC12.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC12, "Please fill in all details")
            Me.txtC12.Focus()

        ElseIf Me.txtC13.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC13, "Please fill in all details")
            Me.txtC13.Focus()

        ElseIf Me.txtC14.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC14, "Please fill in all details")
            Me.txtC14.Focus()
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim das As New DSSlips

                das.Tables("Slips").Rows.Add({0, Branch, "-", 0, ClassOfBuiness, LineOfBuiness, Abbreviation, "Insurance", _
                                              "----------------", "----------------", _
                                              Me.txtC1.Text.Trim, Me.txtC2.Text.Trim, Me.txtC3.Text.Trim, Me.txtC4.Text.Trim, _
                                              Me.txtC5.Text.Trim, Me.txtC6.Text.Trim, Me.txtC7.Text.Trim, Me.txtC8.Text.Trim, _
                                              Me.txtC9.Text.Trim, Me.txtC10.Text.Trim, Me.txtC11.Text.Trim, Me.txtC12.Text.Trim, Me.txtC13.Text.Trim, Me.txtC14.Text.Trim})

                Dim rpt As New SlipMAC
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If

    End Sub

   
End Class